# -*- coding: utf-8 -*-
"""
author:码同学 极光
date:2023-02-01
desc: 
sample: 
"""
import os

import xlrd as xlrd

from util import get_project_path

path = os.path.join(get_project_path(), "files", "test.xls")


def getExcelDataByxlrdList(path, sheet=0):
    wb = xlrd.open_workbook(path)
    sheet = wb.sheet_by_index(sheet)
    # print(sheet.nrows) 行
    # print(sheet.ncols) 列
    # print(sheet.cell_value(1, 0))
    # [[1,success],[2,[],]
    excel_lines = []
    for row in range(1, sheet.nrows):
        excel_line = []
        for col in range(sheet.ncols):
            value = sheet.cell_value(row, col)
            if isinstance(value, float):
                value = int(value)
            excel_line.append(value)
        excel_lines.append(excel_line)
    return excel_lines


# print(getExcelDataByxlrdList(path,1))

# list[{}]
def getExcelDataByxlrdListDict(path, sheet=0):
    wb = xlrd.open_workbook(path)
    sheet = wb.sheet_by_index(sheet)
    # print(sheet.nrows) 行
    # print(sheet.ncols) 列
    excel_lines = []
    for row in range(1, sheet.nrows):
        row_dict = {}
        for col in range(sheet.ncols):
            value = sheet.cell_value(row, col)
            if isinstance(value, float):
                value = int(value)
            row_dict[sheet.cell_value(0, col)] = value
        excel_lines.append(row_dict)
    return excel_lines


#print(getExcelDataByxlrdListDict(path,1))


import pandas as pd
#list[list]
def getExcelListBypandas(path, sheet=0):
    excel= pd.read_excel(path,sheet)
    return excel.values.tolist()

#print(getExcelListBypandas(path,1))

def getExcelListDictBypandas(path, sheet=0):
    excel= pd.read_excel(path,sheet)
    head_list = list(excel.columns)  # 拿到表头列表
    excel_list=[]
    for valuelist in excel.values:
        excel_list.append(dict(zip(head_list,valuelist)))
    return excel_list


print(getExcelListDictBypandas(path))


df = pd.DataFrame([['Tom', 21], ['Bob', 30]], columns=['name', 'age'])
# df.to_excel("output2.xlsx",index=False)
df.to_excel('output3.xlsx',sheet_name='data')
# a=['a','b','c']
# b=['1',2,'3']
# print(list(zip(a,b)))
# print(dict(zip(a,b)))